Re: ISOLATION LEVEL SERIALIZABLE

Wow. RTFM for me huh? Thanks for the heads up folks!

>power2themacs <power2themacs@yahoo.com> writes:
>
>>  >In table two you would not create a serial instead you would create an
>>  >INTEGER because serial is a counter and the values in table 2 may not be
>>  >in table 1.
>>  >
>>  >Use a transaction like as follows
>>  >
>>  >BEGIN;
>>  >INSERT INTO TABLE1 VALUES (Whatever values);
>>  >var = SELECT CURRVAL('sequence_name');
>>  >INSERT INTO TABLE2 VALUES (var,whatever else);
>>  >COMMIT;
>>  >
>>
>>  But this is the race condition I am trying to avoid. Someone can
>>  insert before I get the currval and it will beincremented and this
>>  will result in invalid data. Right now, I'm doing exactly that but I
>>  add SET ISOLATION LEVEL SERIALIZABLE; after the transaction which
>>  locks out other INSERTS. I think I'll try PG Explorer's idea. Thanks
>>  PG Explorer!
>
>Actually currval is precisely what you need.  It will return the
>current value of the sequence in question for your particular backend
>without paying attention to what might be going on in another
>connection.  So the above transaction is perfectly safe, and is, in
>fact, the standard way of writing these sorts of transactions in
>PostgreSQL.
>
>So you can rest assured that I am not making this up, here's the
>relevant bit from the PostgreSQL documentation.
>
>         currval
>
>         Return the value most recently obtained by nextval for this
>         sequence in the current server process. (An error is reported
>         if nextval has never been called for this sequence in this
>         process.) Notice that because this is returning a
>         process-local value, it gives a predictable answer even if
>         other server processes are executing nextval meanwhile.
>
>I hope this is helpful,
>
>Jason


--
><><><><><><><><><><><><
AgentM
agentm@cmu.edu

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com